package com.naiterui.ehp.bs.recommend.repository.mysql;

import java.math.BigInteger;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Set;

import javax.persistence.LockModeType;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.naiterui.common.repository.core.BaseJpaRepository;
import com.naiterui.ehp.bs.recommend.bean.DrMedicationRecomInfo;
import com.naiterui.ehp.bs.recommend.bean.MedicationRecom;

@Repository
public interface IMedicationRecomRepository extends BaseJpaRepository<MedicationRecom, Long> {

    MedicationRecom findFirstBySerialNumber(String serialNumber);

    List<MedicationRecom> findBySerialNumberIn(Collection<String> serialNumbers);

    @Query(value = "select count(id) from MedicationRecom where doctorId = ?1 and patientId = ?2 AND type <> "
        + MedicationRecom.RECOM_TYPE_VIDEO)
    Long getRecomCount(Long doctorId, Long patientId);

    @Query(value = "select count(id) from MedicationRecom where doctorId = ?1 AND type <> "
        + MedicationRecom.RECOM_TYPE_VIDEO)
    Long getRecomCount(Long doctorId);

    MedicationRecom findFirstByDoctorIdAndPatientIdAndExtId(Long doctorId, Long patientId, String extId);

    List<MedicationRecom> findByIdIn(Set<Long> ids);

    List<MedicationRecom> findBySessionIdIn(Set<Long> sessionIds);

    List<MedicationRecom> findByPatientIdAndDoctorIdAndCreatedAtBetween(Long patientId, Long doctorId, Date start,
        Date end);

    List<MedicationRecom> findByPatientIdAndDoctorId(Long patientId, Long doctorId);

    Page<MedicationRecom> findByDoctorIdAndPatientIdInAndTypeNotAndForceRecomNot(Long doctorId, Set<Long> patientIds,
        Integer type, Integer forceRecom, Pageable pageable);

    @Modifying
    @Query(value = "update MedicationRecom SET status = :status WHERE id IN (:recomIds)")
    void updateRecomStatus(@Param("status") Integer status, @Param("recomIds") Collection<Long> recomIds);

    @Query(value =
        " SELECT recom FROM MedicationRecom  recom ,DrMedicationRecomInfo info   WHERE recom.id = info.recommendId AND  info.checkingStatus in ( "
            + DrMedicationRecomInfo.CHECKING_STATUS_DEF + " ," + DrMedicationRecomInfo.CHECKING_STATUS_PASS
            + ") AND recom.forceRecom <>"
            + MedicationRecom.FORCE_RECOM_STATUS_NOT_SEND
            + " AND recom.createdAt >= ?1 AND recom.createdAt < ?2 AND recom.status in ?3")
        List<MedicationRecom> getTimeLimitRecom(Date start, Date end, List<Integer> recomStatus);


    @Query(value =
        " SELECT recom FROM MedicationRecom  recom ,DrMedicationRecomInfo info   WHERE recom.id = info.recommendId AND  info.checkingStatus <> "
            + DrMedicationRecomInfo.CHECKING_STATUS_IN + " AND info.checkingStatus <>"
            + DrMedicationRecomInfo.CHECKING_STATUS_NOT_PASS + " AND recom.forceRecom <>"
            + MedicationRecom.FORCE_RECOM_STATUS_NOT_SEND + " AND recom.status = ?1")
    List<MedicationRecom> getTimeLimitRecom(Integer recomStatus);

    List<MedicationRecom> findByIdIn(Collection<Long> idSet);

    Page<MedicationRecom> findByDoctorIdAndPatientIdInOrderByCreatedAtDesc(Long doctorId, Set<Long> patientIds,
        Pageable pageable);

    Page<MedicationRecom> findByDoctorIdAndPatientIdInAndTypeNotAndForceRecomNotOrderByCreatedAtDesc(Long doctorId,
        Set<Long> patientIds, Integer type, Integer forceRecom, Pageable pageable);

    //{
    //    String idsStr = "0";
    //    if (patientIds != null && patientIds.size() > 0) {
    //        idsStr = patientIds.toString();
    //        idsStr = idsStr.substring(1, idsStr.length() - 1);
    //    }
    //    return super.pagedQuery("from MedicationRecom where doctorId=? and type<>? and patientId in (" + idsStr
    //                    + ") and forceRecom<>? order by createdAt desc", pageVO.getPage(), pageVO.getNum(), doctorId,
    //            MedicationRecom.RECOM_TYPE_SYSTEM,
    //            MedicationRecom.FORCE_RECOM_STATUS_NOT_SEND);
    //}

    // TODO: 2020/4/14
    //Map<Long, MedicationRecom> getMedicationRecomByDoctorIdMap(Long doctorId, Set<Long> expIdSet, Date start, Date end);

    @Query(value = "select distinct(doctorId) from MedicationRecom where createdAt >= ?1 and createdAt <= ?2")
    List<BigInteger> getDoctorIds(String startTime, String endTime);

    @Query(value = "select COUNT(id) from MedicationRecom where doctorId = ?1 and createdAt >= ?2 and createdAt <= ?3")
    Integer getDrNumByDr(Long doctorId, Date start, Date end);

    @Query(value = "select COUNT(id) from MedicationRecom where doctorId = ?1 and createdAt >= ?2 and createdAt <= ?3  and status = ?4")
    Integer getDrNumByDr(Long doctorId, Date start, Date end, Integer status);

    @Query(value = "select COUNT(id) from MedicationRecom where doctorId = ?1")
    Integer getDrNumByDr(Long doctorId);

    // TODO: 2020/4/14
    //Page<MedicationRecom> getPageRecom(Long doctorId, Date start, Date end, Integer status, PageParamVO pageVO);

    // TODO: 2020/4/14
    //Page<MedicationRecom> findRecoms(Long doctorId, PageParamVO pageParam, String startTime, String endTime);

    Page<MedicationRecom> findByPatientIdAndDoctorIdOrderByCreatedAtDesc(Long patientId, Long doctorId,
        Pageable pageable);

    Page<MedicationRecom> findByPatientIdAndDoctorIdAndSessionIdOrderByCreatedAtDesc(Long patientId, Long doctorId,
        Long sessionId, Pageable pageable);

    @Query(value = "select COUNT(id) from MedicationRecom where doctorId = ?1")
    Integer getDrNumByDrNoTime(Long doctorId);

    @Query(value = "select COUNT(id) from MedicationRecom where doctorId = ?1  and status = ?2")
    Integer getDrNumByDrNoTime(Long doctorId, Integer status);

    @Query(value = "select COUNT(id) from MedicationRecom where doctorId = ?1")
    Long getDrNumByDrId(Long doctorId);

    List<MedicationRecom> findByRequireId(Long requireId);

    @Lock(value = LockModeType.PESSIMISTIC_WRITE)
    MedicationRecom findFirstById(Long recomId);

    @Query(value =
        "SELECT alias.* FROM (SELECT recom.* FROM dr_medication_recom recom LEFT JOIN dr_medication_recom_info info ON recom.id = info.recommend_id"
            + " WHERE recom.doctor_id = ? AND recom.patient_id = ? AND recom.force_recom <> ? AND recom.type <> ? AND recom.visiable = ? "
            + " AND recom.invalid <> ? AND (info.checking_status <> ? AND info.checking_status <> ? OR info.checking_status IS NULL) ORDER BY recom.id DESC ) as alias"
        , nativeQuery = true)
    Page<MedicationRecom> getRecomsVisiableByPage(Long doctorId, Long patientId, Pageable pageable);

    List<MedicationRecom> findByRequireIdAndForceRecomInOrderByCreatedAtDesc(Long requireId,
        Collection<Integer> forceRecoms);

  @Query(value = "select r.* from dr_medication_recom r join recom_audit_log a on r.id=a.recom_id and a.status=0 and r.expire_at>now() order by id desc limit 100", nativeQuery = true)
  List<MedicationRecom> getPendingRecom();

    Integer countByDoctorIdAndType(Long doctorId, Integer type);

  /**
   * 根据求药id和状态查询处方信息
   *
   * @param requireIds
   * @param forceRecomStatus
   * @return
   */
  List<MedicationRecom> findByRequireIdInAndForceRecom(Collection<Long> requireIds, Integer forceRecomStatus);

    List<MedicationRecom> findBySessionId(Long sessionId);

    @Query("select t from MedicationRecom t join DrMedicationRecomInfo i on t.id=i.recommendId and i.pharmacistSignStatus = 1 "
        + "and t.patientId = ?1 and t.lastRequireAt is null and ((t.status in (?2)) or (t.status=0 and t.expireAt < ?3)) order by t.createdAt desc")
    Page<MedicationRecom> findByPatientIdAndLastRequireAtIsNullOrderByCreatedAtDesc(Long patientId, Collection<Integer> status, Date expire, Pageable pageable);

    @Query("select t from MedicationRecom t join DrMedicationRecomInfo i on t.id=i.recommendId and i.pharmacistSignStatus = 1 "
            + "and t.patientId = ?1 order by t.createdAt desc")
    Page<MedicationRecom> findByPatientIdOrderByCreatedAtDesc(Long patientId, Pageable pageable);


    @Query("select t from MedicationRecom t join DrMedicationRecomInfo i on t.id=i.recommendId and i.pharmacistSignStatus = 1 "
        + "and t.patientId = ?1 and t.status in (?2) order by t.createdAt desc")
    Page<MedicationRecom> findByPatientIdAndStatusInOrderByCreatedAtDesc(Long patientId, Collection<Integer> status, Pageable pageable);

    Page<MedicationRecom> findByPatientIdAndExpireAtAfterAndStatusInOrderByCreatedAtDesc(Long patientId, Date expire, Collection<Integer> status,
        Pageable pageable);

    @Query("select t from MedicationRecom t join DrMedicationRecomInfo i on t.id=i.recommendId and i.pharmacistSignStatus = 1 "
        + "and t.patientId = ?1 and t.status in (?2) and t.expireAt >= ?3 order by t.createdAt desc")
    Page<MedicationRecom> findByPatientIdAndStatusInAndExpireAtAfterOrderByCreatedAtDesc(Long patientId, Collection<Integer> status, Date expire,
        Pageable pageable);

    List<MedicationRecom> findByCreatedAtBetween(Date startDate, Date endDate);

    List<MedicationRecom> findBySessionIdIn(List<Long> sessionId);
}